{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "file = r'doccao.jsonl'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "outputs": [
    {
     "data": {
      "text/plain": "       global_conversation_id    一级业务类型 二级业务类型             三级业务类型  \\\n0                    16230255  费用问题（平台）  技术服务费  技术服务费纠纷-投诉技术服务费未退   \n1                    16202407  费用问题（平台）  技术服务费  技术服务费纠纷-投诉技术服务费未退   \n2                    16208668  费用问题（平台）  技术服务费  技术服务费纠纷-投诉技术服务费未退   \n3                    16211132  费用问题（平台）  技术服务费  技术服务费纠纷-投诉技术服务费未退   \n4                    16194600  费用问题（平台）  技术服务费  技术服务费纠纷-投诉技术服务费未退   \n...                       ...       ...    ...                ...   \n25725                17512556  费用问题（平台）     运费        运费纠纷-货主拖欠运费   \n25726                17503526  费用问题（平台）     运费        运费纠纷-货主拖欠运费   \n25727                17504148  费用问题（平台）     运费        运费纠纷-货主拖欠运费   \n25728                17495872  费用问题（平台）     运费        运费纠纷-货主拖欠运费   \n25729                17503830  费用问题（平台）     运费        运费纠纷-货主拖欠运费   \n\n          IM_question                                               text  \\\n0          未装货技术服务费未退  智能客服(intelligent_recommend_type): 业务咨询\\n用户(txt...   \n1          未装货技术服务费未退  智能客服(intelligent_recommend_type): 业务咨询\\n用户(txt...   \n2          未装货技术服务费未退  智能客服(intelligent_recommend_type): 业务咨询\\n用户(txt...   \n3          未装货技术服务费未退  用户(txt): \\n智能客服(txt): 小满为你找到以下问题~\\n智能客服(knowle...   \n4          未装货技术服务费未退  智能客服(intelligent_recommend_type): 业务咨询\\n用户(txt...   \n...               ...                                                ...   \n25725  货物已卸，运费货主一直未支付  智能客服(intelligent_recommend_type): 业务咨询\\n用户(txt...   \n25726  货物已卸，运费货主一直未支付  智能客服(position_card): 常见问题（点击下方问题可更快帮助您）\\n智能客服(...   \n25727  货物已卸，运费货主一直未支付  智能客服(intelligent_recommend_type): 业务咨询\\n用户(aud...   \n25728  货物已卸，运费货主一直未支付  智能客服(position_card): 常见问题（点击下方问题可更快帮助您）\\n智能客服(...   \n25729  货物已卸，运费货主一直未支付  智能客服(intelligent_recommend_type): 业务咨询\\n用户(txt...   \n\n       ai_turns  \n0            10  \n1             8  \n2            10  \n3             5  \n4             4  \n...         ...  \n25725        14  \n25726         4  \n25727         4  \n25728         2  \n25729         4  \n\n[25730 rows x 7 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>global_conversation_id</th>\n      <th>一级业务类型</th>\n      <th>二级业务类型</th>\n      <th>三级业务类型</th>\n      <th>IM_question</th>\n      <th>text</th>\n      <th>ai_turns</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>16230255</td>\n      <td>费用问题（平台）</td>\n      <td>技术服务费</td>\n      <td>技术服务费纠纷-投诉技术服务费未退</td>\n      <td>未装货技术服务费未退</td>\n      <td>智能客服(intelligent_recommend_type): 业务咨询\\n用户(txt...</td>\n      <td>10</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>16202407</td>\n      <td>费用问题（平台）</td>\n      <td>技术服务费</td>\n      <td>技术服务费纠纷-投诉技术服务费未退</td>\n      <td>未装货技术服务费未退</td>\n      <td>智能客服(intelligent_recommend_type): 业务咨询\\n用户(txt...</td>\n      <td>8</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>16208668</td>\n      <td>费用问题（平台）</td>\n      <td>技术服务费</td>\n      <td>技术服务费纠纷-投诉技术服务费未退</td>\n      <td>未装货技术服务费未退</td>\n      <td>智能客服(intelligent_recommend_type): 业务咨询\\n用户(txt...</td>\n      <td>10</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>16211132</td>\n      <td>费用问题（平台）</td>\n      <td>技术服务费</td>\n      <td>技术服务费纠纷-投诉技术服务费未退</td>\n      <td>未装货技术服务费未退</td>\n      <td>用户(txt): \\n智能客服(txt): 小满为你找到以下问题~\\n智能客服(knowle...</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>16194600</td>\n      <td>费用问题（平台）</td>\n      <td>技术服务费</td>\n      <td>技术服务费纠纷-投诉技术服务费未退</td>\n      <td>未装货技术服务费未退</td>\n      <td>智能客服(intelligent_recommend_type): 业务咨询\\n用户(txt...</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>25725</th>\n      <td>17512556</td>\n      <td>费用问题（平台）</td>\n      <td>运费</td>\n      <td>运费纠纷-货主拖欠运费</td>\n      <td>货物已卸，运费货主一直未支付</td>\n      <td>智能客服(intelligent_recommend_type): 业务咨询\\n用户(txt...</td>\n      <td>14</td>\n    </tr>\n    <tr>\n      <th>25726</th>\n      <td>17503526</td>\n      <td>费用问题（平台）</td>\n      <td>运费</td>\n      <td>运费纠纷-货主拖欠运费</td>\n      <td>货物已卸，运费货主一直未支付</td>\n      <td>智能客服(position_card): 常见问题（点击下方问题可更快帮助您）\\n智能客服(...</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>25727</th>\n      <td>17504148</td>\n      <td>费用问题（平台）</td>\n      <td>运费</td>\n      <td>运费纠纷-货主拖欠运费</td>\n      <td>货物已卸，运费货主一直未支付</td>\n      <td>智能客服(intelligent_recommend_type): 业务咨询\\n用户(aud...</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>25728</th>\n      <td>17495872</td>\n      <td>费用问题（平台）</td>\n      <td>运费</td>\n      <td>运费纠纷-货主拖欠运费</td>\n      <td>货物已卸，运费货主一直未支付</td>\n      <td>智能客服(position_card): 常见问题（点击下方问题可更快帮助您）\\n智能客服(...</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>25729</th>\n      <td>17503830</td>\n      <td>费用问题（平台）</td>\n      <td>运费</td>\n      <td>运费纠纷-货主拖欠运费</td>\n      <td>货物已卸，运费货主一直未支付</td>\n      <td>智能客服(intelligent_recommend_type): 业务咨询\\n用户(txt...</td>\n      <td>4</td>\n    </tr>\n  </tbody>\n</table>\n<p>25730 rows × 7 columns</p>\n</div>"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(file,orient=\"records\",lines=True)\n",
    "df"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "outputs": [],
   "source": [
    "df.to_excel(\"1.xlsx\")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple\n",
      "Collecting openpyxl\n",
      "  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/0d/89/f78a9a895e221ec8b13ae7f9495f340a0fb43563b13e2891b5df134f20ea/openpyxl-3.1.0-py2.py3-none-any.whl (250 kB)\n",
      "     ------------------------------------ 250.0/250.0 kB 450.8 kB/s eta 0:00:00\n",
      "Collecting et-xmlfile\n",
      "  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/96/c2/3dd434b0108730014f1b96fd286040dc3bcb70066346f7e01ec2ac95865f/et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)\n",
      "Installing collected packages: et-xmlfile, openpyxl\n",
      "Successfully installed et-xmlfile-1.1.0 openpyxl-3.1.0\n"
     ]
    }
   ],
   "source": [
    "!pip install openpyxl"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [],
   "source": [
    "df.to_excel(\"1.xlsx\")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "是    25711\n否       19\nName: is_transfer_manual, dtype: int64"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [
    {
     "data": {
      "text/plain": "global_conversation_id         4948\nai_conversation_id             2841\ncs_conversation_id             4948\ndialogues                      4948\nturns                          4948\nai_turns                       4948\nuser_role                      4906\nuser_id                        4906\nis_transfer_manual             4948\ncrm_record_id                  4948\nmajor_biz_name                 4948\nminor_biz_name                 4948\nminimal_biz_name               4948\nconversation_detail_dc_info    4948\nconversation_record_detail     4948\nday                            4948\nIM_question                    4948\ndtype: int64"
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df[\"ai_turns\"] <= 3) ].count()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "outputs": [
    {
     "data": {
      "text/plain": "global_conversation_id         8205\nai_conversation_id             8205\ncs_conversation_id             8205\ndialogues                      8205\nturns                          8205\nai_turns                       8205\nuser_role                      8205\nuser_id                        8205\nis_transfer_manual             8205\ncrm_record_id                  8205\nmajor_biz_name                 8205\nminor_biz_name                 8205\nminimal_biz_name               8205\nconversation_detail_dc_info    8205\nconversation_record_detail     8205\nday                            8205\nIM_question                    8205\ndtype: int64"
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df[\"ai_turns\"] > 6) ].count()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "file_name = r\"D:\\工作相关内容\\公司项目\\智能机器人\\IM已圈定场景（只交互一次）.xlsx\"\n",
    "import pandas as pd\n",
    "df = pd.read_excel(file_name)\n",
    "df"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n",
     "is_executing": true
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}